#!/usr/bin/perl -l

use strict;

use DBI;

package main;
my $dbh = DBI->connect(
    'DBI:mysql:database=test;host=localhost', 'testik', '123456');

print for @{$dbh->selectcol_arrayref( Tree->new()->sql )};
exit;

package Tree;
sub new {
    my $class = shift;
    my $self = {
        P_ID => shift || 0, # parent id
        DMAX => shift || 5, # max deep hierarchy (may change)
    };
    return bless $self, $class;
}

sub sql {
    my $self = shift;
    
    my @elem;
    push @elem, $self->_make_select($_) for 1..$self->{DMAX};
    return $self->_make_union(@elem);
}

sub _sequen {
    my ($self, $n) = @_;
    return map 'a'.$_, 1 .. $n;
}

sub _field {
    my ($self, $n) = @_;
    
    return 'a1.name' if $n == 1;
    
    my @as = $self->_sequen($n);
    return 
        sprintf('concat(%s)',
            sprintf(join(",' - ',", ('%s.name') x $n), @as));
}

sub _from {
    my ($self, $n) = @_;
    
    return 'tree a1' if $n == 1;
    
    my @as = $self->_sequen($n);
    
    my ($from, @from);
    @from = ('tree a1');
    for (my $i = 1; $i < $n; $i++) {
        push @from, 
            sprintf('    tree %s on (%s.id=%s.p_id)', 
                map $as[$_], $i, $i-1, $i);
    }
    ($from = join "\n    join\n", @from) =~ s/join(?!.*join)/left join/s;
    
    return $from;
}

sub _where {
    my ($self, $n) = @_;
    return sprintf('a1.p_id = %d', $self->{P_ID});
}

sub _make_select {
    my ($self, $n) = @_;
    return 
        sprintf(<<SQL, map $self->$_($n), qw/_field _from _where/);
select %s
  from %s
 where %s
SQL
}

sub _make_union {
    my $self = shift;
    return 
        sprintf(<<SQL, join "\nunion\n", @_);
select name
  from (
%s
       ) x
 where name is not null
 order by 1;
SQL
}

__END__

/* DB setup */
GRANT ALL ON test.* TO 'testik'@'localhost' IDENTIFIED BY '123456';
CREATE TABLE tree (id INT, p_id INT, name VARCHAR(64));
INSERT INTO tree (id, p_id, name) VALUES(1,0,'Root');
INSERT INTO tree (id, p_id, name) VALUES(7,1,'Sub #3');
INSERT INTO tree (id, p_id, name) VALUES(2,1,'Sub #1');
INSERT INTO tree (id, p_id, name) VALUES(3,1,'Sub #2');
INSERT INTO tree (id, p_id, name) VALUES(4,2,'Sub #1a');
INSERT INTO tree (id, p_id, name) VALUES(5,3,'Sub #2a');
INSERT INTO tree (id, p_id, name) VALUES(6,5,'Sub #2b');

